import com.mysql.cj.jdbc.MysqlDataSource;
import com.mysql.cj.x.protobuf.MysqlxCrud;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class J102401 {
    //获取数据源
    static DataSource dataSource = new MysqlDataSource();

    public static class SqlAdd{
        Connection connection = dataSource.getConnection();
        String sql = "insert into students values(?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        public SqlAdd(int id, String name) throws SQLException {
            preparedStatement.setInt(1, id);
            preparedStatement.setString(2, name);
            preparedStatement.executeUpdate();
            preparedStatement.close();
            connection.close();
        }

    }

    public static class SqlDelte{
        Connection connection = dataSource.getConnection();
        String sql = "delete from students where id = ? and name = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        public SqlDelte(int id, String name) throws SQLException{
            preparedStatement.setInt(1, id);
            preparedStatement.setString(2, name);
            preparedStatement.executeUpdate();
            preparedStatement.close();
            connection.close();
        }
    }

    public static class SqlUpdate{
        Connection connection = dataSource.getConnection();
        String sql = "update students set id = ? , name = ? where id = ? and name = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        public SqlUpdate(int nid, String nname, int oid, String oname ) throws SQLException{
            preparedStatement.setInt(1, nid);
            preparedStatement.setString(2, nname);
            preparedStatement.setInt(3, oid);
            preparedStatement.setString(4, oname);
            preparedStatement.executeUpdate();
            preparedStatement.close();
            connection.close();
        }
    }

    public static class SqlSelect{
        Connection connection = dataSource.getConnection();
        String sql = "select * from students";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet;
        public SqlSelect() throws SQLException {
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println(id + " " + name);
            }
            resultSet.close();
            preparedStatement.close();
            connection.close();
        }
    }



    public static void main(String[] args) throws SQLException {
        //建立连接
        ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/students?characterEncoding=utf8&usesL = false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("MYSQL");

        new SqlAdd(1, "张三");
        new SqlDelte(1,"张三");
        new SqlUpdate(1,"张三", 2, "李四");
        new SqlSelect();


    }
}
